package com.tianlan.blog.dao;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import com.tianlan.blog.dto.CreateUser;
import com.tianlan.blog.dto.QueryUser;
import com.tianlan.blog.dto.UpdateUser;
import com.tianlan.blog.dto.UserInfo;
import com.tianlan.blog.entity.SysUser;
import com.tianlan.common.page.MyPage;


/**
 * The Interface UserJpaDao.
 * @author abel
 */
public interface SysUserDao extends JpaRepository<SysUser, Long>,JpaSpecificationExecutor<SysUser>{
		
	@Query(value ="SELECT "
			+ "A.id AS id,"
			+ "A.user_account AS userAccount,"
			+ "A.user_nick_name AS userNickName,"
			+ "A.user_name AS userName,"
			+ "A.user_sex AS userSex,"
			+ "A.user_email AS userEmail,"
			+ "A.user_mobile AS userMobile,"
			+ "A.user_password AS userPassword,"
			+ "A.user_status AS userStatus,"
			+ "A.head_img_url AS headImgUrl,"
			+ "A.province AS province,"
			+ "D.name AS provinceName,"
			+ "A.city AS city,"
			+ "E.name AS cityName,"
			+ "A.area AS area,"
			+ "A.street AS street,"
			+ "A.description AS description,"
			+ "A.create_time AS createTime,"
			+ "A.creator AS creator,"
			+ "A.update_time AS updateTime,"
			+ "A.OPERATOR AS operator,"
			+ "GROUP_CONCAT(DISTINCT C.id) AS roleIds,"
			+ "GROUP_CONCAT(DISTINCT C.role_key) AS roleKey,"
			+ "GROUP_CONCAT(DISTINCT C.role_name) AS roleName,"
			+ "GROUP_CONCAT(DISTINCT F.organization_id) AS dataPermission,"
			+ "G.id AS organizationId,"
			+ "G.organization_name AS organizationName\n"
			+ "FROM sys_user A\n"
			+ "INNER JOIN sys_user_role B ON A.id = B.user_id AND B.del_flag = 0\n"
			+ "LEFT JOIN sys_role C ON B.role_id = C.id AND C.del_flag = 0\n"
			+ "LEFT JOIN sys_district D ON A.province = D.code\n"
			+ "LEFT JOIN sys_district E ON A.city = E.code "
			+ "LEFT JOIN sys_data_permission H ON A.id = H.user_id AND H.del_flag = 0\n"
			+ "INNER JOIN sys_organization_user F ON A.id = F.user_id AND F.del_flag = 0\n"
			+ "INNER JOIN sys_organization G ON F.organization_id = G.id AND G.del_flag = 0\n"
			+ "WHERE A.del_flag = 0\n"
			+ "AND (A.id = :#{#user.id} or :#{#user.id} is null)\n"
			+ "AND (A.user_account like concat('%', :#{#user.userAccount}, '%') or :#{#user.userAccount} is null)\n"
			+ "AND (A.user_nick_name like concat('%', :#{#user.userNickName}, '%') or :#{#user.userNickName} is null)\n"
			+ "AND (A.user_name like concat('%', :#{#user.userName}, '%') or :#{#user.userName} is null)\n"
			+ "AND (A.user_mobile like concat('%', :#{#user.userMobile}, '%') or :#{#user.userMobile} is null)\n"
			+ "AND (A.user_email like concat('%', :#{#user.userEmail}, '%') or :#{#user.userEmail} is null)\n"
			+ "AND (A.user_status = :#{#user.userStatus} or :#{#user.userStatus} is null or :#{#user.userStatus} = '')\n"
			+ "AND (B.role_id = :#{#user.roleId} or :#{#user.roleId} is null)\n"
			+ "AND (G.id = :#{#user.organizationId} or :#{#user.organizationId} is null) "
			+ "AND (DATE_FORMAT(A.create_time,'%Y-%m-%d') >= DATE_FORMAT(:#{#user.startDate},'%Y-%m-%d') or :#{#user.startDate} is null)\n"
			+ "AND (DATE_FORMAT(A.create_time,'%Y-%m-%d') <= DATE_FORMAT(:#{#user.endDate},'%Y-%m-%d') or :#{#user.endDate} is null)\n"
			+ "GROUP BY A.id\n"
			+ "ORDER BY A.create_time DESC LIMIT :#{#page.start},:#{#page.size}"
	,nativeQuery = true)
	List<Object[]> queryUserInfoList(@Param("user") QueryUser user,@Param("page") MyPage<UserInfo> page);
	
	@Query(value ="SELECT DISTINCT COUNT(1) "
			+ "FROM sys_user A\n"
			+ "INNER JOIN sys_user_role B ON A.id = B.user_id AND B.del_flag = 0\n"
			+ "LEFT JOIN sys_role C ON B.role_id = C.id AND C.del_flag = 0\n"
			+ "LEFT JOIN sys_district D ON A.province = D.code\n"
			+ "LEFT JOIN sys_district E ON A.city = E.code "
			+ "LEFT JOIN sys_data_permission H ON A.id = H.user_id AND H.del_flag = 0\n"
			+ "INNER JOIN sys_organization_user F ON A.id = F.user_id AND F.del_flag = 0\n"
			+ "INNER JOIN sys_organization G ON F.organization_id = G.id AND G.del_flag = 0\n"
			+ "WHERE A.del_flag = 0\n"
			+ "AND (A.id = :#{#user.id} or :#{#user.id} is null)\n"
			+ "AND (A.user_account like concat('%', :#{#user.userAccount}, '%') or :#{#user.userAccount} is null)\n"
			+ "AND (A.user_nick_name like concat('%', :#{#user.userNickName}, '%') or :#{#user.userNickName} is null)\n"
			+ "AND (A.user_name like concat('%', :#{#user.userName}, '%') or :#{#user.userName} is null)\n"
			+ "AND (A.user_mobile like concat('%', :#{#user.userMobile}, '%') or :#{#user.userMobile} is null)\n"
			+ "AND (A.user_email like concat('%', :#{#user.userEmail}, '%') or :#{#user.userEmail} is null)\n"
			+ "AND (A.user_status = :#{#user.userStatus} or :#{#user.userStatus} is null or :#{#user.userStatus} = '')\n"
			+ "AND (B.role_id = :#{#user.roleId} or :#{#user.roleId} is null)\n"
			+ "AND (G.id = :#{#user.organizationId} or :#{#user.organizationId} is null) "
			+ "AND (DATE_FORMAT(A.create_time,'%Y-%m-%d') >= DATE_FORMAT(:#{#user.startDate},'%Y-%m-%d') or :#{#user.startDate} is null)\n"
			+ "AND (DATE_FORMAT(A.create_time,'%Y-%m-%d') <= DATE_FORMAT(:#{#user.endDate},'%Y-%m-%d') or :#{#user.endDate} is null)\n"
			+ "ORDER BY A.create_time DESC"
	,nativeQuery = true)
	int queryUserInfoListCount(@Param("user") QueryUser user);
	
	SysUser findByUserAccountAndDelFlag(String userAccount,String delFlag);
	
	@Query(value = "SELECT count(1) FROM sys_user WHERE del_flag = '0' AND user_account = ?#{#user.userAccount} OR user_nick_name = ?#{#user.userNickName} "
			+ "OR user_email = ?#{#user.userEmail} OR user_mobile = ?#{#user.userMobile} "
	,nativeQuery = true)
	Integer getIsUser(@Param("user") CreateUser user);
	
	@Query(value = "SELECT count(1) FROM sys_user WHERE del_flag = '0' AND user_account = ?#{#user.userAccount} AND user_nick_name = ?#{#user.userNickName} "
			+ "AND user_email = ?#{#user.userEmail} AND user_mobile = ?#{#user.userMobile}"
	,nativeQuery = true)
	Integer getIsUser(@Param("user") UpdateUser user);
	
	@Transactional
	@Modifying
	@Query(value = "UPDATE sys_user SET user_account = :#{#user.userAccount}, user_nick_name = :#{#user.userNickName},user_name = :#{#user.userName},"
			+ "user_sex = :#{#user.userSex},user_email = :#{#user.userEmail},user_mobile = :#{#user.userMobile},"
			+ "user_status = :#{#user.userStatus},province= :#{#user.province},city= :#{#user.city},"
			+ "area = :#{#user.area},street= :#{#user.street},description = :#{#user.description},"
			+ "update_time = :#{#user.updateTime},operator = :#{#user.operator} WHERE id = :#{#user.id}",nativeQuery = true)
	int updateUser(@Param("user") SysUser user);
	
	@Transactional
	@Modifying
	@Query(value = "UPDATE sys_user SET del_flag = 1 WHERE id = ?1",nativeQuery = true)
	int deleteUser(Integer id);
	
}